#coding:utf-8
import os
import pandas as pd
from openpyxl.reader.excel import load_workbook


def dealpilog(file_log,deviceid):  #统计1 3 4 2状态个数
    count1, count3, count4, count2, count = 0, 0, 0, 0, 0
    with open(file_log, 'r') as file:
        for line in file:
            # 空格分割
            content = line.split()
            # 获取最后一位
            last_char = content[-1]
            # 获取id
            id = content[-3]
            # 获取time
            #time = content[-4]
            #print(id, last_char)
            if id == deviceid:
                count1 += 1 if last_char == '1' else 0
                count3 += 1 if last_char == '3' else 0
                count4 += 1 if last_char == '4' else 0
                count2 += 1 if last_char == '2' else 0

            count = count1 + count3 + count4+ count2
    #print(deviceid,"—— 1："+str(count1),"3："+ str(count3),"4："+ str(count4),"2："+ str(count2),"一共"+ str(count))
    return deviceid, count1, count3, count4, count2, count


def reqpitime(file_log,num): #获取写入数据库的time  num最大值 如果time大于num输出
    with open(file_log, 'r') as file:
        for line in file:
            # 空格分割
            content = line.split()
            # 获取time
            timestr = content[-4]
            time = int(timestr.split('=')[1])
           # print(time)
            if time>num:
                print(line)


def writeExcel(file_excel,file_log,deviceid):
    # 检查文件是否存在
    sheet_name = 'xxx'
    deviceid, count1, count3, count4, count2, count = dealpilog(file_log, deviceid)
    new_data = pd.DataFrame({
        'device_id': [deviceid],
        '1': [count1],
        '3': [count3],
        '4': [count4],
        '2': [count2],
        'total': [count],
    })
    if os.path.exists(file_excel):
        # 加载现有的Excel文件
        book = load_workbook(filename=file_excel)
        # 假设我们要写入的是第一个工作表，也可以根据需要选择其他工作表
        sheet = book.active
        mode = 'a'  # 追加模式
        if_sheet_exists = 'overlay'  # 已存在工作表时，追加数据
        book.close()  # 关闭工作簿以释放资源
    else:
        mode = 'w'  # 写入模式
        if_sheet_exists = None  # 不需要设置，因为工作表不存在

    # 写入Excel文件，如果文件不存在，ExcelWriter将创建它
    with pd.ExcelWriter(file_excel, engine='openpyxl', mode=mode, if_sheet_exists=if_sheet_exists) as writer:
        # 写入DataFrame到Excel的下一个空白列
        new_data.to_excel(writer, index=False, sheet_name=sheet_name)


def writeExcel2(file_excel, file_log, deviceid):
    # 检查文件是否存在
    if not os.path.exists(file_excel):
        # 文件不存在，创建一个新的Excel文件
        df = pd.DataFrame()
        df.to_excel(file_excel, index=False)  # 写入Excel文件
        print("文件不存在，已创建新文件。")
    else:
        deviceid, count1, count3, count4, count2, count = dealpilog(file_log, deviceid)
        df = pd.read_excel(file_excel)  # 读取Excel文件
        new_data = pd.DataFrame({
            'device_id': [deviceid],
            '1': [count1],
            '3': [count3],
            '4': [count4],
            '2': [count2],
            'total': [count],
        })
        df = pd.concat([df, new_data], ignore_index=True)  # 将新数据添加到原有数据中
        df.to_excel(file_excel, index=False)  # 写入Excel文件
        print("数据已写入。")



file_log= "test1.log"
# reqpitime(file_name,1000)
file_excel = 'pilogstat.xlsx'

devicelist = ['1595'
,'1721'
,'172D'
,'1756'
,'17A3'
,'1D53'
,'1DA4'
,'4015'
,'4080'
,'40A2'
,'41A4'
,'420A'
,'422B'
,'42C6'
,'441F'
,'45A8'
,'4684'
,'4E01'
,'4E73'
,'4F19'
,'501F'
,'50A6'
,'50AD'
,'511B'
,'5120'
,'514A'
,'528B'
,'5347'
,'534D'
,'5364'
,'53AB'
,'53C4'
,'549C'
,'54A8'
,'5534'
,'5548'
,'5595'
,'55A0'
,'55B6'
,'562B'
,'58B2'
,'D18E']
for deviceid in devicelist:
    writeExcel2(file_excel,file_log,deviceid)


